package com.jiezheng.huinong.biz;

import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.jiezheng.huinong.constant.FileBizType;
import com.jiezheng.huinong.constant.ImportErrorType;
import com.jiezheng.huinong.dao.ApplyManageDAOImpl;
import com.jiezheng.huinong.dao.CommonInfoDAOImpl;
import com.jiezheng.huinong.dao.IndexManageDAOImpl;
import com.jiezheng.huinong.dao.PaperManageDAOImpl;
import com.jiezheng.huinong.dao.PeopleManageDAOImpl;
import com.jiezheng.huinong.model.ApplyDetail;
import com.jiezheng.huinong.model.ApplyInfo;
import com.jiezheng.huinong.model.ImportInfo;
import com.jiezheng.huinong.model.IndexInfo;
import com.jiezheng.huinong.model.OperationStatus;
import com.jiezheng.huinong.model.PeopleInfo;
import com.jiezheng.huinong.util.CommonUtils;

public class ImportFile {

	/**
	 * 居民信息模板
	 */
	public static final int ACCOUNT_COL = 0;
	public static final int BANK_COL = 1;
	public static final int NAME_COL = 2;
	public static final int ID_CARD_COL = 3;
	public static final int ADDRESS_COL = 4;
	public static final int TEL_COL = 5;
	public static final int MEMO_COL = 6;

	/**
	 * 申请信息模板
	 */
	// public static final int APPLY_ACCOUNT_COL = 0;
	public static final int APPLY_NAME_COL = 0;
	// public static final int APPLY_BANK_COL = 2;
	// public static final int APPLY_ADDRESS_COL = 4;
	public static final int APPLY_ID_CARD_COL = 1;
	public static final int APPLY_ACCOUNT_COL = 2;
	public static final int APPLY_BUDGET_ITEM_COL = 3;
	public static final int APPLY_MONEY_COL = 4;

	/**
	 * 指标信息模板
	 */
	public static final int INDEX_NO_COL = 0;
	public static final int BUDGET_UNIT_COL = 1;
	public static final int BUDGET_ITEM_COL = 2;
	public static final int INDEX_SOURCE_COL = 3;
	public static final int INDEX_TYPE_COL = 4;
	public static final int FUND_PROP_COL = 5;
	public static final int PAY_METHOD_COL = 6;
	public static final int SETTLE_METHOD_COL = 7;
	public static final int FUNC_TYPE_COL = 8;
	public static final int FINANCIAL_TYPE_COL = 9;
	public static final int BUDGET_TYPE_COL = 10;
	public static final int MONEY_COL = 11;
	public static final int PURPOSE_COL = 12;

	public static Map<String, String> convertDataToMap(
			List<HashMap<String, String>> data) {

		Map<String, String> map = new HashMap<String, String>();
		for (HashMap<String, String> item : data) {
			map.put(item.get("name"), item.get("id"));
		}
		return map;
	}

	public static String convertMoneyString(String money) {
		Double moneyDouble = 0.0d;
		if (!money.isEmpty()) {
			moneyDouble = Double.valueOf(money);
		}
		return String.format("%.2f", moneyDouble);
	}

	/**
	 * 导入居民信息
	 * 
	 * @param path
	 * @return
	 */
	public static ImportInfo importPeopleFile(String path, String deptNo) {

		// 统计数据
		ImportInfo info = new ImportInfo();

		// 取得所有的身份证信息
		List<String> _idCards = (new PeopleManageDAOImpl()).getAllIdCards();
		Set<String> dbIdCards = new HashSet<String>(_idCards);
		Set<String> localIdCards = new HashSet<String>();

		// 取得数据
		ExcelFileParser parser = null;
		try {
			parser = new ExcelFileParser(path, FileBizType.PEOPLE);
		} catch (FileNotFoundException e) {
			System.out.println("importPeopleFile error!");
		}

		Map<Integer, ArrayList<String>> data = parser.getContent();
		int totalDataSize = data.size();
		int dataSize = totalDataSize;
		info.setAllLines(totalDataSize);

		// 转化为对象
		List<PeopleInfo> peoples = new ArrayList<PeopleInfo>();
		for (int i = 1; i <= totalDataSize; i++) {
			List<String> row = data.get(i);

			// 存在空行
			if (row == null || row.isEmpty()) {
				dataSize = dataSize - 1;
				continue;
			}

			PeopleInfo people = new PeopleInfo();
			people.setAccount(row.get(ACCOUNT_COL));
			people.setIdCard(row.get(ID_CARD_COL));
			people.setBank(row.get(BANK_COL));
			people.setName(row.get(NAME_COL));
			people.setMemo(row.get(MEMO_COL));
			people.setAddress(row.get(ADDRESS_COL));
			people.setTel(row.get(TEL_COL));
			people.setStatus(OperationStatus.TO_AUDIT);
			people.setDeptNo(deptNo);

			String id = people.getIdCard();

			// 数据库中已存在
			if (dbIdCards.contains(id)) {
				dataSize = dataSize - 1;
				continue;
			}

			// 之前的数据中已存在
			if (localIdCards.contains(id)) {
				dataSize = dataSize - 1;
				continue;
			} else {
				localIdCards.add(id);
			}

			peoples.add(people);
		}
		info.setImportLines(dataSize);

		// 如果没有数据，或者存在非法数据，提前退出
		if (totalDataSize == 0 || dataSize != totalDataSize) {
			return info;
		}

		// 数据不存在问题，将全部入库
		for (PeopleInfo people : peoples) {
			(new PeopleManageDAOImpl()).addPeople(people);
		}

		return info;
	}

	/**
	 * 导入指标信息
	 * 
	 * @param path
	 * @return
	 */
	public static ImportInfo importIndexFile(String path, String user,
			String deptNo) {

		// 统计数据
		ImportInfo info = new ImportInfo();

		// 取得数据
		ExcelFileParser parser = null;
		try {
			parser = new ExcelFileParser(path, FileBizType.INDEX);
		} catch (FileNotFoundException e) {
			System.out.println("importIndexFile error!");
		}
		Map<Integer, ArrayList<String>> data = parser.getContent();

		// 转化为对象
		List<IndexInfo> indexes = new ArrayList<IndexInfo>();
		for (int i = 1; i <= data.size(); i++) {
			List<String> row = data.get(i);
			if (row == null || row.isEmpty()) {
				continue;
			}

			IndexInfo index = new IndexInfo();
			index.setIndexNo(row.get(INDEX_NO_COL));
			index.setBudgetItem(row.get(BUDGET_ITEM_COL));
			index.setBudgetUnit(row.get(BUDGET_UNIT_COL));
			index.setIndexSource(row.get(INDEX_SOURCE_COL));
			index.setIndexType(row.get(INDEX_TYPE_COL));
			index.setFundProp(row.get(FUND_PROP_COL));
			index.setPayMethod(row.get(PAY_METHOD_COL));
			index.setFuncType(row.get(FUNC_TYPE_COL));
			index.setSettleMethod(row.get(SETTLE_METHOD_COL));
			index.setFinancialType(row.get(FINANCIAL_TYPE_COL));
			index.setBudgetType(row.get(BUDGET_TYPE_COL));
			String money = row.get(MONEY_COL);
			index.setMoney(ImportFile.convertMoneyString(money));
			index.setSpareMoney(index.getMoney());
			index.setPurpose(row.get(PURPOSE_COL));
			index.setDeptNo(deptNo);

			indexes.add(index);
		}

		int dataSize = indexes.size();
		info.setAllLines(dataSize);

		// 如果没有数据，提前退出
		if (dataSize == 0) {
			info.setImportLines(0);
			return info;
		}

		// 检验数据并入库
		verifyIndexData(indexes, info, user);

		return info;
	}

	private static void verifyIndexData(List<IndexInfo> indexes,
			ImportInfo info, String user) {
		// 增加指标相关数据Name->id的映射的加载
		List<String> _indexNo = (new PaperManageDAOImpl()).listIndexNo();
		Map<String, String> _budgetItem = convertDataToMap((new CommonInfoDAOImpl())
				.listBudgetItemNameId());
		Map<String, String> _budgetUnit = convertDataToMap((new CommonInfoDAOImpl())
				.listBudgetUnitNameId());
		Map<String, String> _indexSource = convertDataToMap((new CommonInfoDAOImpl())
				.listIndexSourceNameId());
		Map<String, String> _indexType = convertDataToMap((new CommonInfoDAOImpl())
				.listIndexTypeNameId());
		Map<String, String> _fundProp = convertDataToMap((new CommonInfoDAOImpl())
				.listFundPropNameId());
		Map<String, String> _payMethod = convertDataToMap((new CommonInfoDAOImpl())
				.listPayMethodNameId());
		Map<String, String> _funcType = convertDataToMap((new CommonInfoDAOImpl())
				.listFuncTypeNameId());
		Map<String, String> _financialType = convertDataToMap((new CommonInfoDAOImpl())
				.listFinancialTypeNameId());
		Map<String, String> _settleMethod = convertDataToMap((new CommonInfoDAOImpl())
				.listSettleMethodNameId());
		Map<String, String> _budgetType = convertDataToMap((new CommonInfoDAOImpl())
				.listBudgetTypeNameId());

		int dataSize = indexes.size();
		// 检查数据的合法性
		for (IndexInfo idx : indexes) {

			// 指标文号
			int pos = _indexNo.indexOf(idx.getIndexNo());
			if (pos == -1 || idx.getIndexNo().isEmpty()) {
				dataSize = dataSize - 1;
				continue;
			}

			// 预算单位
			String budgetUnit = _budgetUnit.get(idx.getBudgetUnit());
			if (budgetUnit == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setBudgetUnit(budgetUnit);
			idx.setBudgetUnitNo(idx.getBudgetUnit());

			// 预算项目
			String budgetItem = _budgetItem.get(idx.getBudgetItem());
			if (budgetItem == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setBudgetItem(budgetItem);
			idx.setBudgetItemNo(idx.getBudgetItem());

			// 指标来源
			String indexSource = _indexSource.get(idx.getIndexSource());
			if (indexSource == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setIndexSource(indexSource);

			// 指标类型
			String indexType = _indexType.get(idx.getIndexType());
			if (indexType == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setIndexType(indexType);

			// 资金类型
			String fundProp = _fundProp.get(idx.getFundProp());
			if (fundProp == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setFundProp(fundProp);

			// 支付方式
			String payMethod = _payMethod.get(idx.getPayMethod());
			if (payMethod == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setPayMethod(payMethod);

			// 功能分类
			String funcType = _funcType.get(idx.getFuncType());
			if (funcType == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setFuncType(funcType);

			// 经济分类
			String financialType = _financialType.get(idx.getFinancialType());
			if (financialType == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setFinancialType(financialType);

			// 结算方式
			String settleMethod = _settleMethod.get(idx.getSettleMethod());
			if (settleMethod == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setSettleMethod(settleMethod);

			// 预算分类
			String budgetType = _budgetType.get(idx.getBudgetType());
			if (budgetType == null) {
				dataSize = dataSize - 1;
				continue;
			}
			idx.setBudgetType(budgetType);

			// 存在性验证

			idx.setBudgetUnitNo(idx.getBudgetUnit());
			IndexInfo _idx = (new IndexManageDAOImpl()).getIndexInfo(idx);
			if (_idx != null) {
				dataSize = dataSize - 1;
				continue;
			}

			// 增加录入时间与录入人
			idx.setOperator(user);
			idx.setCreateTime(CommonUtils.getCurrentDate());
			idx.setStatus(OperationStatus.TO_AUDIT);

			// 有效的数据入库
			(new IndexManageDAOImpl()).addIndex(idx);
		}

		info.setImportLines(dataSize);
	}

	/**
	 * 导入申请信息
	 * 
	 * @param path
	 * @return
	 */
	public static ImportInfo importApplyFile(String path, String budgetUnit) {

		// 取得所有的预算项目
		Map<String, String> _budgetItem = convertDataToMap((new CommonInfoDAOImpl())
				.listBudgetItemNameId());

		// 统计数据
		ImportInfo info = new ImportInfo();

		// 取得数据
		ExcelFileParser parser = null;
		try {
			parser = new ExcelFileParser(path, FileBizType.APPLY);
		} catch (FileNotFoundException e) {
			System.out.println("importApplyFile error!");
		}
		Map<Integer, ArrayList<String>> data = parser.getContent();

		// 转化为对象
		String theBudgetItem = "";
		double totalMoney = 0.0;

		// 统计信息
		int totalDataSize = data.size();
		int dataSize = totalDataSize;
		info.setAllLines(totalDataSize);

		List<ApplyDetail> applies = new ArrayList<ApplyDetail>();
		for (int i = 1; i <= totalDataSize; i++) {
			List<String> row = data.get(i);
			//空行检验
			if (row == null || row.isEmpty()) {
				dataSize = dataSize -1;
				info.getDetailLines().add(i);
				info.getDetailError().put(i, ImportErrorType.EMPTY_LINE);
				continue;
			}

			ApplyDetail apply = new ApplyDetail();
			apply.setApplyNo("");
			apply.setName(row.get(APPLY_NAME_COL));
			apply.setIdCard(row.get(APPLY_ID_CARD_COL));
			apply.setAccount(row.get(APPLY_ACCOUNT_COL));
			apply.setBudgetItem(row.get(APPLY_BUDGET_ITEM_COL));
			String money = row.get(APPLY_MONEY_COL);
			apply.setMoney(ImportFile.convertMoneyString(money));
			applies.add(apply);

			// 设置预算项目
			String budgetItem = _budgetItem.get(apply.getBudgetItem());
			if (budgetItem == null) {
				dataSize = dataSize - 1;
				info.getDetailLines().add(i);
				info.getDetailError().put(i, ImportErrorType.BUDGET_ITEM_INVALID);
				continue;
			}
			apply.setBudgetItem(budgetItem);

			// 生成预算项目
			if (theBudgetItem.isEmpty()) {
				theBudgetItem = budgetItem;
			}

			// 校验预算项目合法性
			if (!theBudgetItem.equals(apply.getBudgetItem())) {
				dataSize = dataSize - 1;
				info.getDetailLines().add(i);
				info.getDetailError().put(i, ImportErrorType.BUDGET_ITEM_ERROR);
				continue;
			}

			// 校验用户的合法性
			PeopleInfo people = (new PeopleManageDAOImpl()).getPeopleInfo(apply.getIdCard());

			if (people == null || people.getStatus() != OperationStatus.AUDITED ) {
				dataSize = dataSize - 1;
				info.getDetailLines().add(i);
				info.getDetailError().put(i, ImportErrorType.NO_PEOPLE);
				continue;
			}
			
			// 检验用户对应的账户的合法性
			if( !people.getAccount().equals(apply.getAccount())){
				dataSize = dataSize - 1;
				info.getDetailLines().add(i);
				info.getDetailError().put(i, ImportErrorType.ACCOUNT_INFO_ERROR);

				continue;
			}

			// 计算总金额
			totalMoney += Double.valueOf(apply.getMoney());
		}
		info.setImportLines(dataSize);
		
		// 如果没有数据，或数据无效, 提前退出, 数据不导入
		if (dataSize == 0 || dataSize != totalDataSize) {
			return info;
		}

		// 数据入库
		String curApplyNo = ApplyManage.getMaxApplyNo();
		ApplyInfo applyInfo = new ApplyInfo();
		applyInfo.setApplyNo(curApplyNo);
		applyInfo.setBudgetItem(theBudgetItem);
		applyInfo.setMoney(String.format("%.2f", totalMoney));
		String budgetUnitSub = String.format("%s001", budgetUnit);
		applyInfo.setBudgetUnit(budgetUnitSub);
		applyInfo.setStatus(OperationStatus.NO_JONIT);
		applyInfo.setDeptNo(budgetUnit);
		applyInfo.setApplyDate(CommonUtils.getCurrentDate());

		(new ApplyManageDAOImpl()).addApply(applyInfo);
		for (ApplyDetail apply : applies) {
			apply.setApplyNo(curApplyNo);
			(new ApplyManageDAOImpl()).addApplyDetail(apply);
		}

		info.setImportLines(dataSize);
		return info;

	}
}
